-- INSERT INTO dbo.t_ReleaseObjects( Parent, Scope)VALUES( 1, 'G')
-- p_InsReleaseObj "S",123,"admin",0,"JAN","PMO","PMO","PMO","ADMIN","ADMIN","ADMIN","PM","O","PMO","12-12-2010","NA"
-- p_InsReleaseObj "M",123,"admin",0,"JAN","PMO","PMO","SvnPath","PMO","ADMIN","ADMIN","ADMIN","PM","O","PMO","12-12-2010","NA"
use common
go
IF OBJECT_ID('dbo.p_InsReleaseObj') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.p_InsReleaseObj
    IF OBJECT_ID('dbo.p_InsReleaseObj') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.p_InsReleaseObj >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.p_InsReleaseObj >>>'
END
go
create proc p_InsReleaseObj
(
	@Scope				varchar(2),	-- internal, external
	@ArtfactId			int	,
	@Contact			varchar(30),	--  Contact
	@OrderId			int	,	--  OrderId
	@ReleaseName			varchar(30),	-- customer short name
	@DBName				varchar(30),	-- customer long name
	@ObjName			varchar(200),	-- ObjName
	@ObjType			varchar(30),	-- ObjName
	@Repopath			varchar(200),	-- ObjName
	@UserUpdated			varchar(50),	-- Fee Contact: Fixed (R)ate/Flat (F)ee
	@DateUpdated			varchar(50),	-- profit share	multiplier
	@ContactPerson			varchar(50),	-- ContactPerson participant
	@Valid				char(1),	-- (Y)es/(N)o/(R)ule
 	@CR_Reason			varchar(250) 		-- preferred customer order to do the returns (institutions only)
	
) as
begin
	-- declares
    
	declare @Msg 		varchar(255)	-- last error message
	declare @ErrVal		int		-- last error number
	declare @GCuId		int
	declare @SCuId		int
	declare @MCuId		int
	declare @CuId		int

	
	select @ErrVal=0
	-- process
	begin tran p_InsReleaseObj
		select @ErrVal=0
		select @OrderId = OrderId from common..t_ObjectType where ObjType = @ObjType
		/* for Release name */
		IF @Scope='S' 
		BEGIN
			SELECT @GCuId = CuId 
			FROM  t_ReleaseObjects
			WHERE Scope ='G'
			

			insert into t_ReleaseObjects 
			( Parent, Scope,ArtfactId,OrderId, ReleaseName,
			DBName, ObjName,ObjType, UserUpdated, DateUpdated,
			Repopath,  ContactPerson, Valid, CR_Reason)

			values( @GCuId, @Scope,@ArtfactId,@OrderId, @ReleaseName,
			@DBName, @ObjName,@ObjType, @UserUpdated, @DateUpdated,
			@Repopath, @ContactPerson, @Valid, @CR_Reason)

			
		
		END
		/* Artfact ID */
		ELSE IF @Scope='M' 
		BEGIN
			SELECT @SCuId = CuId 
			FROM  t_ReleaseObjects
			WHERE Scope ='S'AND ReleaseName = @ReleaseName

			insert into t_ReleaseObjects 
			(  Parent, Scope,ArtfactId,OrderId, ReleaseName,
			DBName, ObjName,ObjType, UserUpdated, DateUpdated,
			Repopath, ContactPerson, Valid, CR_Reason)

			values( @SCuId, @Scope, @ArtfactId,@OrderId, @ReleaseName,
			@DBName, @ObjName,@ObjType,  @UserUpdated, @DateUpdated,
			@Repopath, @ContactPerson, @Valid,  @CR_Reason)

			select @CuId = max(CuId) from t_ReleaseObjects
			UPDATE t_ReleaseObjects SET ArtfactId = @CuId where CuId = @CuId
		END

		ELSE 
		/* for each release object node*/
		BEGIN
			SELECT @MCuId = CuId 
			FROM  t_ReleaseObjects
			WHERE Scope ='M'
			AND ReleaseName = @ReleaseName
			AND ArtfactId = @ArtfactId

			insert into t_ReleaseObjects 
			(  Parent, Scope,ArtfactId,OrderId, ReleaseName,
			DBName, ObjName,ObjType, UserUpdated, DateUpdated,
			Repopath, ContactPerson, Valid,  CR_Reason)

			values( @MCuId , @Scope,@ArtfactId,@OrderId, @ReleaseName,
			@DBName, @ObjName,@ObjType,  @UserUpdated, @DateUpdated,
			@Repopath, @ContactPerson, @Valid, @CR_Reason)

			select @ErrVal=@@error
			
		END

		if @ErrVal!=0
		begin
			rollback tran p_InsReleaseObj
			select @ErrVal=99999,@Msg='Failed to insert new Oject:' + @ObjName
			goto ErrorHandler
		end
	commit tran p_InsReleaseObj

	
	return 0 

ErrorHandler:
	raiserror @ErrVal @Msg
End

go
EXEC sp_procxmode 'dbo.p_InsReleaseObj','unchained'
go
IF OBJECT_ID('dbo.p_InsReleaseObj') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.p_InsReleaseObj >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.p_InsReleaseObj >>>'
go
GRANT EXECUTE ON dbo.p_InsReleaseObj TO public
go